<?php

/*
 * Copyright (C) 2006 - 2010 Pham Cong Dinh
 *
 * This file is part of Spica.
 *
 * This is free software; you can redistribute it and/or modify it
 * under the terms of the GNU Lesser General Public License as
 * published by the Free Software Foundation; either version 3 of
 * the License, or (at your option) any later version.
 *
 * This software is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
 * Lesser General Public License for more details.
 *
 * You should have received a copy of the GNU Lesser General Public
 * License along with this software; if not, write to the Free
 * Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA
 * 02110-1301 USA, or see the FSF site: http://www.fsf.org.
*/

/**
 * A connection (session) with a MySQL database
 *
 * @category   spica
 * @package    core
 * @subpackage datasource\db\mysql
 * @author     Pham Cong Dinh <pcdinh at phpvietnam dot net>
 * @since      Version 0.3
 * @since      October 18, 2008
 * @copyright  Pham Cong Dinh (http://www.phpvietnam.net)
 * @license    http://www.gnu.org/licenses/lgpl-3.0.txt
 * @version    $Id: Connection.php 1795 2010-05-04 10:41:58Z pcdinh $
 */

/**
 * Connection interface
 */
require_once 'library/spica/core/datasource/db/Connection.php';

/**
 * Result set interface
 */
require_once 'library/spica/core/datasource/db/ResultSet.php';

/**
 * @see http://svn.mysql.com/svnpublic/php-mysqlnd/trunk/php5/ext/
 */
class SpicaMySQLConnection implements SpicaConnection
{
    /**
     * Database host
     *
     * @var string
     */
    protected $_dbhost;

    /**
     * Database user name
     *
     * @var string
     */
    protected $_dbuser;

    /**
     * Database name
     *
     * @var string
     */
    protected $_db;

    /**
     * Database password associated with the above database user
     *
     * @var string
     */
    protected $_dbpassword;

    /**
     * Database port that database server used
     *
     * @var int default to 3306
     */
    protected $_dbport = 3306;

    /**
     * Database socket, something like "/tmp/mysql.sock"
     *
     * @var string
     */
    protected $_dbsocket;

    /**
     * Character set we employ for all of the database manipulation operation
     *
     * @var string
     */
    protected $_dbcharset = 'utf8';

    /**
     * MySQL connection flags
     *
     * MYSQLI_CLIENT_COMPRESS    	Use compression protocol
     * MYSQLI_CLIENT_FOUND_ROWS 	return number of matched rows, not the number of affected rows
     * MYSQLI_CLIENT_IGNORE_SPACE 	Allow spaces after function names. Makes all function names reserved words.
     * MYSQLI_CLIENT_INTERACTIVE 	Allow interactive_timeout seconds (instead of wait_timeout seconds) of inactivity before closing the connection
     * MYSQLI_CLIENT_SSL            Use SSL (encryption)
     *
     * @see http://vn2.php.net/mysqli_real_connect
     * @var int
     */
    protected $_dbflag = null;

    /**
     * Has the connections closed
     *
     * @var bool
     */
    protected $_isClosed = false;

    /**
     * Is the connection protected from updating, deleting, inserting or replacing data
     *
     * @var bool
     */
    protected $_readOnly = false;

    /**
     * An object of mysqli that keeps a native database connection to a MySQL Server
     *
     * @var mysqli
     */
    protected $_connection = null;

    /**
     * An array of open statements that is created by createStatement()
     *
     * @var array
     */
    protected $_openStatements = array();

    /**
     * Is debug mode on?
     *
     * @var bool
     */
    protected $_debugMode = false;

    /**
     * Determine if MySQL database server should commit the action result right after
     * the update/delete/insert query executed or not
     *
     * The default behaviour is to use transaction
     *
     * @var bool
     */
    protected $_autoCommit = false;

    /**
     * Transaction isolation level.
     *
     * @var string
     */
    protected $_isolationLevel = 'READ COMMITTED';

    /**
     * Has the transaction been started
     *
     * @var bool
     */
    protected $_transactionStarted = false;

    /**
     * The number of currently open transactions
     *
     * @var int
     */
    protected $_transactionCount = 0;

    /**
     * Has this connection supported multiple queries sent to server as a single one
     *
     * @var bool
     */
    protected $_allowedMultipleQueries = true;

    /**
     * Has connection been initialized with mysqli_init()?
     *
     * @var bool
     */
    protected $_connectionInitialized = false;

    /**
     * Has connection been established with mysqli_real_connect?
     *
     * @var bool
     */
    protected $_connectionEstablished = false;

    /**
     * The largest packet we can send (changed once we know what the server supports, we get this at connection init)
     *
     * @var int
     */
    protected $_maxAllowedPacket = 65536;

    /**
     * Max re-connecting attempts
     *
     * @var int
     */
    protected $_maxReconnects = 3;

    /**
     * Identify if mixed case unquoted SQL identifiers can be treated as lower case, upper case or mixed case
     *
     * Value can be: SpicaResultSet::IDENTIFIER_LOWERCASE
     *               SpicaResultSet::IDENTIFIER_UPPERCASE
     *               SpicaResultSet::IDENTIFIER_MIXEDCASE
     * @var int
     */
    protected $_sqlIdentifierCase = SpicaResultSet::IDENTIFIER_LOWERCASE;

    /**
     * Constructs a <code>SpicaMySQLConnection</code> object
     *
     * @throws InvalidArgumentException
     * @param  array $params
     * @param  bool  $persistent
     * @return SpicaMySQLConnection
     */
    public function __construct($params = array(), $persistent = false)
    {
        if (false === is_array($params) || empty($params))
        {
            throw new InvalidArgumentException('The first parameter to '.__CLASS__.' must be an array that contains database access settings.');
        }

        if (false === isset($params['dbhost']))
        {
            throw new InvalidArgumentException('Class '.__CLASS__.' requires you to set up index "dbhost" in params array.');
        }

        if (false === isset($params['db']))
        {
            throw new InvalidArgumentException('Class '.__CLASS__.' requires you to set up index "db" in params array.');
        }

        if (false === isset($params['dbuser']))
        {
            throw new InvalidArgumentException('Class '.__CLASS__.' requires you to set up index "dbuser" in params array.');
        }

        if (false === isset($params['dbpassword']))
        {
            throw new InvalidArgumentException('Class '.__CLASS__.' requires you to set up index "dbpassword" in params array.');
        }

        $this->_dbhost = (string) $params['dbhost'];

        // @see http://blogs.sun.com/mandalika/entry/demonstrating_the_features_of_mysql
        if (true === $persistent)
        {
            $this->_dbhost = 'p:'.$this->_dbhost;
        }

        $this->_db         = (string) $params['db'];
        $this->_dbuser     = (string) $params['dbuser'];
        $this->_dbpassword = (string) $params['dbpassword'];

        // Optional
        if (true === isset($params['dbcharset']))
        {
            $this->_dbcharset = (string) $params['dbcharset'];
        }

        // Optional
        if (true === isset($params['dbport']))
        {
            $this->_dbport = (string) $params['dbport'];
        }

        // Optional
        if (true === isset($params['dbsocket']))
        {
            $this->_dbsocket = (string) $params['dbsocket'];
        }

        // Optional
        if (true === isset($params['dbflag']))
        {
            $this->_dbflag = (int) $params['dbflag'];
        }

        // Optional. dbreport = off/error/strict/index/all
        if (true === isset($params['dbreport']) && 'off' === $params['dbreport'])
        {
            if (in_array($params['dbreport'], array('all', 'off', 'index', 'error', 'strict')))
            {
                mysqli_report(constant('MYSQLI_REPORT_'.strtoupper($params['dbreport'])));
            }
            else
            {
                // Turn off irritating default messages
                mysqli_report(MYSQLI_REPORT_OFF);
            }
        }

        // Prepare a mysqli object that is ready to make a real connection to database server when neccessary
        $this->_prepareConnection();

        if (true === isset($params['dbssl']))
        {
            mysqli_ssl_set($this->_connection,
                empty($params['dbssl.key'])    ? null : $params['dbssl.key'],
                empty($params['dbssl.cert'])   ? null : $params['dbssl.cert'],
                empty($params['dbssl.ca'])     ? null : $params['dbssl.ca'],
                empty($params['dbssl.capath']) ? null : $params['dbssl.capath'],
                empty($params['dbssl.cipher']) ? null : $params['dbssl.cipher']
            );
        }
    }

    /**
     * Gets the SQL identifier case mode
     *
     * Value can be: SpicaResultSet::IDENTIFIER_LOWERCASE
     *               SpicaResultSet::IDENTIFIER_UPPERCASE
     *               SpicaResultSet::IDENTIFIER_MIXEDCASE
     * @return int
     */
    public function getSQLIdentifierCase()
    {
        return $this->_sqlIdentifierCase;
    }

    /**
     * Sets the SQL identifier case mode.
     *
     * @param int $case
     *        + ResultSet::IDENTIFIER_LOWERCASE
     *        + ResultSet::IDENTIFIER_UPPERCASE
     *        + ResultSet::IDENTIFIER_MIXEDCASE
     */
    public function setSQLIdentifierCase($case)
    {
        $this->_sqlIdentifierCase = $case;
    }

    /**
     * Sets this connection's auto-commit mode
     *
     * By default, MySQL runs with autocommit mode enabled
     *
     * If a connection is in auto-commit mode, then all its SQL statements will be executed and committed
     * as individual transactions
     *
     * Otherwise, its SQL statements are grouped into transactions that are terminated by a call to either
     * the method commit or the method rollback
     *
     * By default, new connections are in auto-commit mode
     *
     * The commit occurs when the statement completes or the next execute occurs, whichever comes first
     *
     * In the case of statements returning a SpicaResultSet, the statement completes when the last row of the
     * SpicaResultSet has been retrieved or the SpicaResultSet has been closed
     *
     * In advanced cases, a single statement may return multiple results as well as output parameter values
     *
     * In these cases the commit occurs when all results and output parameter values have been retrieved
     *
     * @param bool $mode
     */
    public function setAutoCommit($autoCommit)
    {
        if (true === $this->_connectionInitialized)
        {
            mysqli_autocommit($this->_connection, $autoCommit);
            $this->_autoCommit = (bool) $autoCommit;
        }
    }

    /**
     * Sets connection timeout
     *
     * @uses  MySQL specific
     * @param int $time Seconds
     */
    public function setTimeout($time)
    {
        if (true === $this->_connectionInitialized)
        {
            mysqli_options($this->_connection, MYSQLI_OPT_CONNECT_TIMEOUT, $time);
        }
    }

    /**
     * Sets config file for database server connection
     *
     * @param string $path
     */
    public function setDefaultServerConfFile($path)
    {
        if (true === $this->_connectionInitialized)
        {
            mysqli_options($this->_connection, MYSQLI_READ_DEFAULT_FILE, $path);
        }
    }

    /**
     * Attempts to change the transaction isolation level for this Connection object to the one given
     *
     * @param int $isolationLevel
     */
    public function setTransactionIsolation($isolationLevel)
    {
        $this->_isolationLevel = $isolationLevel;
    }

    /**
     * Gets low level database connection
     *
     * @throws SpicaDatabaseException when database connection is closed
     * @return mysqli
     */
    public function getNativeConnection()
    {
        $fresh     = false;
        if (false  === $this->_connectionEstablished)
        {
            $this->_establishConnection();
            $fresh = true;
        }

        if (true   === $fresh)
        {
            return $this->_connection;
        }

        try
        {
            $this->_checkForClosedConnection();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Database connection is closed', null, null, null, $ex);
        }

        return $this->_connection;
    }

    /**
     * Sets the charset on the current connection
     *
     * MySQLi specific
     *
     * @param string $charset
     */
    public function setCharset($charset)
    {
        if (true === $this->_connectionInitialized)
        {
            mysqli_set_charset($this->_connection, $charset);
        }

        throw new SpicaDatabaseException('Unable to set server character set because connection is not initialized yet.', null, null, null);
    }

    /**
     * Gets MySQL server client charset endcoding
     *
     * @see    SpicaConnection#getClientCharset()
     * @return string
     */
    public function getClientCharset()
    {
        if (true === $this->_connectionInitialized)
        {
            return mysqli_character_set_name($this->_connection);
        }

        throw new SpicaDatabaseException('Unable to get client character set because connection is not initialized yet.', null, null, null);
    }

    /**
     * Registers a SpicaMySQLStatement instance as open statement
     *
     * @see   SpicaConnection#registerStatement()
     * @param SpicaMySQLStatement $stmt the MySQLStatement instance to remove
     */
    public function registerStatement($stmt)
    {
        $this->_openStatements[] = $stmt;
    }

    /**
     * Removes the given statement from the list of open statements
     *
     * @see   SpicaConnection#unregisterStatement()
     * @param SpicaMySQLStatement $stmt the MySQLStatement instance to remove
     */
    public function unregisterStatement($stmt)
    {
        if (true === empty($this->_openStatements))
        {
            return;
        }

        foreach ($this->_openStatements as $pos => $openStmt)
        {
            if ($stmt === $openStmt)
            {
                unset($this->_openStatements[$pos]);
                break;
            }
        }
    }

    /**
     * Creates a <code>SpicaMySQLStatement</code> object for sending SQL statements to the database
     *
     * SQL statements without parameters are normally executed using Statement objects
     * If the same SQL statement is executed many times, it may be more efficient to use a MySQLPreparedStatement object
     *
     * @param  int $identifierCase Field name case sensitiveness defaults to IDENTIFIER_LOWERCASE
     * @return SpicaMySQLStatement
     */
    public function createStatement($identifierCase = null)
    {
        // Checking for closed connection is kept until query execution time
        if (null !== $identifierCase)
        {
            $this->_sqlIdentifierCase = $identifierCase;
        }

        require_once 'library/spica/core/datasource/db/mysql/Statement.php';
        return new SpicaMySQLStatement($this);
    }

    /**
     * Creates a <code>SpicaMySQLMultipleQueryStatement</code> object for sending
     * many SQL statements to the database
     *
     * SQL statements without parameters are normally executed using Statement objects
     * If the same SQL statement is executed many times, it may be more efficient to use a MySQLPreparedStatement object
     *
     * @param  int $identifierCase Field name case sensitiveness defaults to IDENTIFIER_LOWERCASE
     * @return SpicaMySQLMultipleQueryStatement
     */
    public function createMultipleQueryStatement($identifierCase = null)
    {
        // Checking for closed connection is kept until query execution time
        if (null !== $identifierCase)
        {
            $this->_sqlIdentifierCase = $identifierCase;
        }

        require_once 'library/spica/core/datasource/db/mysql/MultipleQueryStatement.php';
        return new SpicaMySQLMultipleQueryStatement($this);
    }

    /**
     * Creates a SpicaMySQLPreparedStatement object for sending
     * parameterized SQL statements to the database.
     *
     * A SQL statement with or without IN parameters can be pre-compiled and stored
     * in a SpicaMySQLPreparedStatement object.
     *
     * This object can then be used to efficiently execute this statement multiple times.
     *
     * The following statements can be used as prepared statements:
     *  + CREATE TABLE, DELETE, DO, INSERT, REPLACE, SELECT, SET, UPDATE, and most SHOW statements.
     *  + Other statements are not supported in MySQL 5.0
     *
     * @see    http://bugs.php.net/bug.php?id=40852
     * @param  string $sql
     * @return SpicaMySQLPreparedStatement
     */
    public function prepareStatement($sql)
    {
        // Checking for closed connection is kept until query execution time
        require_once 'library/spica/core/datasource/db/mysql/PreparedStatement.php';
        return new SpicaMySQLPreparedStatement($this, $sql);
    }

    /**
     * Creates a statement from a function or stored procedure
     *
     * @param  string $sql
     * @return SpicaMySQLCallableStatement
     */
    public function prepareCall($sql)
    {
        // Checking for closed connection is kept until query execution time
        require_once 'library/spica/core/datasource/db/mysql/CallableStatement.php';
        return new SpicaMySQLCallableStatement($this, $sql);
    }

    /**
     * Begins a transaction
     *
     * @see   SpicaMySQLConnection::_establishConnection()
     * @param string $savepoint A savepoint name
     */
    public function beginTransaction($savepoint = null)
    {
        // Connect to the database if there is no established connection
        $this->_establishConnection();

        // Check if the established connection is active or not
        try
        {
            $this->_checkForClosedConnection();
        }
        catch (SpicaDatabaseException $ex)
        {
            throw new SpicaDatabaseException('Unable to begin a transaction because database connection is closed', null, null, $ex);
        }

        $this->_autoCommit = false;

        if (null  !== $savepoint)
        {
            $query    = 'SAVEPOINT '.$savepoint;
            $result   = mysqli_query($this->_connection, $query);

            if (false === $result)
            {
                throw new SpicaDatabaseException('Unable to create a savepoint named '.$savepoint, mysqli_error($this->_connection), mysqli_sqlstate($this->_connection), mysqli_errno($this->_connection));
            }
        }
        else
        {
            $result   = mysqli_query($this->_connection, 'START TRANSACTION');

            if (false === $result)
            {
                throw new SpicaDatabaseException('Unable to begin a transaction. ', mysqli_error($this->_connection), mysqli_sqlstate($this->_connection), mysqli_errno($this->_connection));
            }
        }

        $this->_transactionStarted = true;
        $this->_transactionCount++;
    }

    /**
     * Commits a transaction
     *
     * @param string $savepoint A savepoint name
     */
    public function commit($savepoint = null)
    {
        if ($this->_transactionCount > 0)
        {
            try
            {
                $this->_checkForClosedConnection();
            }
            catch (SpicaDatabaseException $ex)
            {
                throw new SpicaDatabaseException('Unable to commit database changes because database connection is closed', null, null, null, $ex);
            }

            $result   = mysqli_commit($this->_connection);

            if (false === $result)
            {
                throw new SpicaDatabaseException('Unable to commit database changes in the current transaction. ', mysqli_error($this->_connection), mysqli_sqlstate($this->_connection), mysqli_errno($this->_connection));
            }

            if (null !== $savepoint)
            {
                $query    = 'RELEASE SAVEPOINT '.$savepoint;

                $result   = mysqli_query($this->_connection, $query);

                if (false === $result)
                {
                    throw new SpicaDatabaseException('Unable to release the savepoint named '.$savepoint.' after the commit. ', mysqli_error($this->_connection), mysqli_sqlstate($this->_connection), mysqli_errno($this->_connection));
                }
            }

            $this->_transactionCount--;
        }

        if (0 === $this->_transactionCount)
        {
            $this->_transactionStarted = false;
        }
    }

    /**
     * Cancels any database changes done during a transaction or since a specific
     * savepoint that is in progress
     *
     * This function may only be called when auto-committing is disabled, otherwise it will fail
     * Therefore, a new transaction is implicitly started after cancelling the pending changes
     *
     * @throws SpicaDatabaseException when database connection is closed, when the rollback is unsuccessful
     * @param  string $savepoint A savepoint name
     */
    public function rollback($savepoint = null)
    {
        if (true === $this->_autoCommit)
        {
            throw new SpicaDatabaseException('Rollback operation is not supported when autocommit mode is turned on');
        }

        if ($this->_transactionCount > 0)
        {
            try
            {
                $this->_checkForClosedConnection();
            }
            catch (SpicaDatabaseException $ex)
            {
                throw new SpicaDatabaseException('Unable to rollback database changes because database connection is closed', null, null, null, $ex);
            }

            $openStatementCount = count($this->_openStatements);

            if ($openStatementCount > 0)
            {
                $lastStatement  = $this->_openStatements[$openStatementCount - 1];

                // if the last statement is active
                $active     = false;

                if (true    === is_object($lastStatement))
                {
                    $active = !$lastStatement->isClosed();
                }

                $result     = null;

                if (null   !== $savepoint)
                {
                    $result = mysqli_query($this->_connection, 'ROLLBACK TO SAVEPOINT '.$savepoint);

                    if (false === $result)
                    {
                        throw new SpicaDatabaseException('Unable to rollback to the savepoint named '.$savepoint, mysqli_error($this->_connection), mysqli_sqlstate($this->_connection), mysqli_errno($this->_connection));
                    }
                }
                else
                {
                    if (true      === $active)
                    {
                        $result   = mysqli_rollback($this->_connection);

                        if (false === $result)
                        {
                            throw new SpicaDatabaseException('Unable to rollback the current transaction', mysqli_error($this->_connection), mysqli_sqlstate($this->_connection), mysqli_errno($this->_connection));
                        }
                    }
                }

                $this->_transactionCount--;
            }
        }

        if (0 === $this->_transactionCount)
        {
            $this->_transactionStarted = false;
        }
    }

    /**
     * @see   SpicaConnection#releaseSavepoint(Savepoint)
     * @param string $savepoint
     */
    public function releaseSavepoint($savepoint)
    {
        // this is a no-op
    }

    /**
     * Gets the current auto-commit state
     *
     * @see    SpicaMySQLConnection::setAutoCommit
     * @return Current state of auto-commit
     */
    public function getAutoCommit()
    {
        return $this->_autoCommit;
    }

    /**
     * Checks if the connection is closed
     *
     * @return bool
     */
    public function isClosed()
    {
        return $this->_isClosed;
    }

    /**
     * Tests to see if the connection is in Read Only Mode
     *
     * Note that we cannot really put the database in read only mode,
     * but we pretend we can by returning the value of the readOnly flag
     *
     * @return true if the connection is read only
     */
    public function isReadOnly()
    {
        return $this->_readOnly;
    }

    /**
     * Puts this connection in read-only mode as a hint to enable database optimizations
     *
     * Note: This method cannot be called while in the middle of a transaction
     *
     * @param bool $readOnly
     */
    public function setReadOnly($readOnly)
    {
        $this->_readOnly = (bool) $readOnly;
    }

    /**
     * Clones the current connection object <code>SpicaMySQLConnection</code>
     *
     * @return SpicaMySQLConnection
     */
    public function duplicate()
    {
        return clone $this;
    }

    /**
     * Has this connection supported multiple queries as a single one
     *
     * @return bool
     */
    public function getAllowMultiQueries()
    {
        return $this->_allowedMultipleQueries;
    }

    /**
     * Escapes special characters in a string for use in a SQL statement
     *
     * This method prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a
     *
     * @param  string $variable
     * @return string
     */
    public function quote($variable)
    {
        if (false === $this->_connectionEstablished)
        {
            $this->_establishConnection();
        }

        return mysqli_real_escape_string($this->_connection, $variable);
    }

    /**
     * Tests if the current connection is in a transaction
     *
     * @return bool
     */
    public function isInTransaction()
    {
        return $this->_transactionStarted;
    }

    /**
     * Checks if the physical connection to database server is active or not
     *
     * @see    SpicaMySQLConnection::_ping()
     * @throws SpicaDatabaseException When the database connection is not active
     */
    protected function _checkForClosedConnection()
    {
        if (true === $this->_isClosed)
        {
            throw new SpicaDatabaseException('The connection to database server is closed. ');
        }

        if (false === $this->_ping())
        {
            $this->_isClosed = true;
            throw new SpicaDatabaseException('The connection to database server is closed unexpectly. ');
        }
    }

    /**
     * Checks if server is alive
     *
     * @return bool
     */
    protected function _ping()
    {
        if (true === $this->_connectionEstablished)
        {
            return mysqli_ping($this->_connection);
        }

        return false;
    }

    /**
     * Closes the connection
     *
     */
    public function close()
    {
        $this->_realClose(true, true);
    }

    /**
     * Initializes MySQLi and making a resource for use with mysqli_real_connect()
     *
     * @see MySQLConnection::_establishConnection()
     */
    protected function _prepareConnection()
    {
        // Create a mysqli object
        $this->_connection = mysqli_init($this->_dbhost, $this->_dbuser, $this->_dbpassword, null, $this->_dbport, $this->_dbflag);
        $this->_connectionInitialized = true;
    }

    /**
     * Makes a connection to a MySQL server
     *
     * @throws SpicaDatabaseException when connection can not establish or the given database/schema is unable to access
     * @see    MySQLConnection::_prepareConnection()
     */
    protected function _establishConnection()
    {
        if (false === $this->_connectionInitialized)
        {
            $this->_prepareConnection();
        }

        if (false === $this->_connectionEstablished)
        {
            // Establish a connection to a MySQL database engine
            $success  = mysqli_real_connect($this->_connection, $this->_dbhost, $this->_dbuser, $this->_dbpassword, null, $this->_dbport, $this->_dbsocket, $this->_dbflag);

            // check connection
            if (false === $success)
            {
                $errorCode = mysqli_connect_errno();

                if (2002   === $errorCode)
                {
                    throw new SpicaConnectionTimeoutException('Unable to connect to database server before connection timeout occurs', mysqli_connect_error(), null, mysqli_connect_errno());
                }

                $message = $this->_getMessageByCode($errorCode);
                throw new SpicaDatabaseException($message, mysqli_connect_error(), null, mysqli_connect_errno());
            }
            else
            {
                $dbSelected = mysqli_select_db($this->_connection, $this->_db);

                if (false   === $dbSelected)
                {
                    throw new SpicaDatabaseException('We were able to connect to the MySQL database server but unable to access the given database. ',
                    mysqli_error($this->_connection), null, mysqli_errno($this->_connection));
                }
                else
                {
                    mysqli_set_charset($this->_connection, $this->_dbcharset);
                }
            }

            $this->_connectionEstablished = true;
            $this->_isClosed              = false;
        }
    }

    /**
     * Gets application message depending on MySQL error code
     *
     * @param  int $code
     * @return string
     */
    protected function _getMessageByCode($code)
    {
        switch ($code)
        {
            case 1045:
                // error 1045 for access denied for user account
                $message = 'Unable to connect to database server. This either means that the username and password information in the website settings is incorrect or we can\'t contact the MySQL database server through the mysqli libraries. This could also mean the current hosting provider\'s database server is down';
                break;

            default:
                // Find all database connection errors (>= 2000)
                $message = 'Unable to connect to database server. Configuration for database server access may be not valid or the database server is not available at the moment. ';
        }

        return $message;
    }

    /**
     * Closes all currently open statement
     *
     */
    protected function closeAllOpenStatements()
    {
        if (null !== $this->_openStatements)
        {
            for ($i = 0, $size = count($this->_openStatements); $i < $size; $i++)
            {
                $stmt = $this->_openStatements[$i];

                $result = $stmt->close();
            }
        }
    }

    /**
     * Closes the database connection physically
     *
     * @throws SpicaDatabaseException when a transaction is found to be open (not commit or rollback)
     * @param  bool $calledExplicitly
     * @param  bool $issueRollback
     */
    protected function _realClose($calledExplicitly, $issueRollback)
    {
        if (false === $this->_isClosed)
        {
            if (false === $this->_autoCommit && $issueRollback)
            {
                $this->rollback();
            }
        }

        if (true === $this->_transactionStarted)
        {
            throw new SpicaDatabaseException('There is a transaction that is open previously. You have not executed commit() or rollback() against this transaction yet. ');
        }

        $this->closeAllOpenStatements();
        $this->_openStatements = array();
        mysqli_close($this->_connection);
        $this->_isClosed = true;
    }

    /**
     * Destroys the current <code>MySQLConnection</code> object
     */
    public function __destruct()
    {
        // If PHP exits due to a code bug during a transaction, an InnoDB table can remain locked until Apache is restarted
        // Release transaction lock
        if (true === $this->_transactionStarted)
        {
            $this->rollback();
        }
    }
}

/**
 * This class provides a set of utility method to work with MySQL dialect SQL commands
 *
 * @category   spica
 * @package    core
 * @subpackage datasource\db\mysql
 * @author     Pham Cong Dinh <pcdinh at phpvietnam dot net>
 * @since      Version 0.1
 * @since      February 28, 2009
 * @copyright  Pham Cong Dinh (http://www.phpvietnam.net)
 * @license    http://www.gnu.org/licenses/lgpl-3.0.txt
 * @version    $Id: Connection.php 1795 2010-05-04 10:41:58Z pcdinh $
 */
class SpicaMySQLCommand extends SpicaDatabaseCommand
{
    /**
     * List of string quoting symbols used in MySQL
     *
     * @var array
     */
    public static $stringQuotes = array('start' => "'", 'end' => "'", 'escape' => '\\', 'escape_pattern' => '\\');

    /**
     * List of field name quoting symbols used in MySQL
     *
     * @var array
     */
    public static $identifierQuotes = array('start' => '`', 'end' => '`', 'escape' => '`');

    /**
     * List of SQL commenting symbols used in MySQL
     *
     * @see SpicaMySQLCommand::skipComments
     * @var array
     */
    public static $sqlComments = array(
        array('start' => '-- ', 'end' => "\n", 'escape' => false),
        array('start' => '#', 'end' => "\n", 'escape' => false),
        array('start' => '/*', 'end' => '*/', 'escape' => false)
    );

    /**
     * Returns the first offset of a query that is not within a SQL comment
     *
     * @param  string $sql
     * @param  int    $position
     * @return int
     */
    public static function skipComments($sql, $position = 0)
    {
        return self::_skipBlocks($sql, $position, self::$sqlComments);
    }

    /**
     * Appends a database-specific LIMIT clause to SQL command
     *
     * @see   SELECT Syntax on MySQL 5.0 Reference Manual
     * @param string $sql
     * @param int    $limit  The total rows to retrieve. This value defaults to null
     * @param int    $offset This value defaults to null
     */
    public static function modifyLimitQuery($sql, $limit = null, $offset = null)
    {
        // LIMIT offset, row_count
        if (null !== $limit && null !== $offset)
        {
            $sql .= ' LIMIT '.$limit.' OFFSET '.$offset; // LIMIT row_count OFFSET offset
        }
        elseif (null !== $limit && null === $offset)
        {
            $sql .= ' LIMIT '.$limit; // LIMIT row_count
        }
        elseif (null === $limit && null !== $offset)
        {
            $sql .= ' LIMIT 999999999999 OFFSET '.$offset; // LIMIT row_count OFFSET offset
        }

        return $sql;
    }

    /**
     * Creates an INSERT statement from an array of data and field mapping
     *
     * @param  SpicaMySQLConnection $conn
     * @param  string               $tableName
     * @param  array                $fields [field => value]
     * @return string An INSERT query
     */
    public static function createInsertStatement($conn, $tableName, $fields)
    {
        $values = array();

        foreach ($fields as $field => $value)
        {
            $fields[] = $field ;

            if (null  === $value)
            {
                $values[] = 'NULL';
                continue;
            }

            if (true === is_object($value))
            {
                $values[] = (string) $value; // SpicaQueryExpression awareness
            }
            else
            {
                $values[] = '\'' . $conn->quote($value) . '\'';
            }
        }

        return 'INSERT INTO ' . $tableName . ' (' . implode(', ', $fields) . ') VALUES (' . implode(', ', $values) . ')';
    }

    /**
     * Creates an INSERT statement from an array of data and field mapping
     *
     * @throws InvalidArgumentException
     * @param  SpicaMySQLConnection $conn
     * @param  string               $tableName
     * @param  array                $rows [field => value]
     * @return string An INSERT query
     */
    public static function createBatchInsertStatement($conn, $tableName, $rows)
    {
        if (false === isset($rows[0]))
        {
            throw new InvalidArgumentException('The second parameter $rows must contain at least a single mapping field-value');
        }

        foreach ($rows[0] as $field => $value)
        {
            $fields[] = $field ;
        }

        $values = array();

        foreach ($rows as $row)
        {
            $v = array();

            foreach ($row as $field => $value)
            {
                if (null === $value)
                {
                    $v[] = 'NULL';
                    continue;
                }

                if (true === is_object($value))
                {
                    $v[] = (string) $value; // SpicaQueryExpression awareness
                }
                else
                {
                    $v[] = '\'' . $conn->quote($value) . '\'';
                }
            }

            $values[] = '(' . implode(', ', $v) . ')';
        }

        return 'INSERT INTO ' . $tableName . ' (' . implode(', ', $fields) . ') VALUES ' . implode(', ', $values);
    }

    /**
     * Creates a WHERE phrase from an array or a string
     *
     * @param  array|string $where
     * @param  SpicaConnection $conn
     * @return string|false
     */
    public static function buildWherePhrase($where, $conn)
    {
        if (true  === is_array($where))
        {
            $cond = array();

            foreach ($where as $field => $value)
            {
                if (null === $value)
                {
                    $cond[] = '`' . $field . '` IS NULL';
                    continue;
                }

                // Check quoted string. If you dont quote your string, we understand that you will keep it as is
                if (false === $firstQuoted = strpos($value, "'"))
                {
                    // Build a string like: `id` > 3
                    $cond[] = '`' . $field . '` ' . $value; // $value = ' > 3'
                }
                else
                {
                    // Build a string like: `id` = 'anc \'s \" \\'
                    // Only quoted strings will need real escape (need some thought of XSS/SQL Injection here)
                    // preg_replace_callback can be used here but do you think that it is too complicated for such a simple thing
                    $cond[] = '`' . $field . '` ' . substr($value, 0, $firstQuoted)."'".$conn->quote(substr($value, $firstQuoted + 1, strlen($value) - 1))."'";
                }
            }

            $where = '';

            if (count($cond) > 0)
            {
                $where  = ' WHERE '.implode(' AND ', $cond);
            }

            return $where;
        }

        if (strlen(trim($where)) >= 3) // i=2

        {
            return ' WHERE '.$where;
        }

        return false;
    }
}

/**
 * The class providers set of common methods that both static statement and prepared statement share.
 *
 * @category   spica
 * @package    core
 * @subpackage datasource\db\mysql
 * @author     Pham Cong Dinh <pcdinh at phpvietnam dot net>
 * @since      Version 0.1
 * @since      January 23, 2008
 * @copyright  Pham Cong Dinh (http://www.phpvietnam.net)
 * @license    http://www.gnu.org/licenses/lgpl-3.0.txt
 */
class SpicaMySQLCommonStatement extends SpicaCommonStatement
{
    /**
     * Checks if the statement object can execute a query that leads to data modification
     *
     * @throws SpicaDatabaseException when database connection is closed or read-only
     */
    protected function _checkUpdatable()
    {
        if (true === $this->_dbConn->isReadOnly())
        {
            throw new SpicaDatabaseException('Connection is read-only.
            Queries leading to data modification are not allowed. ');
        }

        if (true === $this->_isClosed)
        {
            throw new SpicaDatabaseException('Unable to execute the query because the statement is closed. ');
        }
    }

    /**
     * Checks if the statement object is closed
     *
     * @throws SpicaDatabaseException when the statement is closed
     */
    protected function _checkClosed()
    {
        if (true === $this->_isClosed)
        {
            throw new SpicaDatabaseException('Unable to execute the query because the query statement is closed. ');
        }
    }

    /**
     * Gets application level connection object in use
     *
     * @return SpicaMySQLConnection
     */
    public function getConnection()
    {
        return $this->_dbConn;
    }

    /**
     * Gets the low level connection object
     *
     * @return resource mysqli connection
     */
    public function getNativeConnection()
    {
        return $this->_dbConn->getNativeConnection();
    }

    /**
     * Checks if the statement is closed
     *
     * @return bool
     */
    public function isClosed()
    {
        return $this->_isClosed;
    }

    /**
     * Closes the cursor and the statement
     *
     * @return bool
     */
    public function close()
    {
        return $this->_realClose(true);
    }
}

?>